package org.example.managere.repository.feibiao;

import org.example.managere.entity.feibiao.AmArchivesFailure;
import org.example.managere.dto.feibiao.AmArchivesFailureDto;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Optional;

@Repository
public interface AmArchivesFailureRepository extends JpaRepository<AmArchivesFailure, Integer> {
    
    /**
     * 直接查询DTO对象，避免懒加载问题
     */
    @Query("SELECT new org.example.managere.dto.feibiao.AmArchivesFailureDto(" +
           "f.failureId, m.motorId, f.failureTime, f.failureType, f.failureDescription, " +
           "f.failureCause, f.repairTime, f.repairMeasures, m.motorType, m.manufacturer) " +
           "FROM AmArchivesFailure f LEFT JOIN f.motorId m")
    Page<AmArchivesFailureDto> findAllDto(Pageable pageable);
    
    /**
     * 根据ID直接查询DTO对象
     */
    @Query("SELECT new org.example.managere.dto.feibiao.AmArchivesFailureDto(" +
           "f.failureId, m.motorId, f.failureTime, f.failureType, f.failureDescription, " +
           "f.failureCause, f.repairTime, f.repairMeasures, m.motorType, m.manufacturer) " +
           "FROM AmArchivesFailure f LEFT JOIN f.motorId m " +
           "WHERE f.failureId = :failureId")
    Optional<AmArchivesFailureDto> findDtoById(@Param("failureId") Integer failureId);
    
    /**
     * 根据电机ID直接查询DTO列表
     */
    @Query("SELECT new org.example.managere.dto.feibiao.AmArchivesFailureDto(" +
           "f.failureId, m.motorId, f.failureTime, f.failureType, f.failureDescription, " +
           "f.failureCause, f.repairTime, f.repairMeasures, m.motorType, m.manufacturer) " +
           "FROM AmArchivesFailure f LEFT JOIN f.motorId m " +
           "WHERE m.motorId = :motorId")
    List<AmArchivesFailureDto> findDtoByMotorId(@Param("motorId") String motorId);
    
    /**
     * 根据故障类型直接查询DTO列表
     */
    @Query("SELECT new org.example.managere.dto.feibiao.AmArchivesFailureDto(" +
           "f.failureId, m.motorId, f.failureTime, f.failureType, f.failureDescription, " +
           "f.failureCause, f.repairTime, f.repairMeasures, m.motorType, m.manufacturer) " +
           "FROM AmArchivesFailure f LEFT JOIN f.motorId m " +
           "WHERE f.failureType LIKE %:failureType%")
    List<AmArchivesFailureDto> findDtoByFailureType(@Param("failureType") String failureType);
    
    /**
     * 根据电机ID查询故障记录
     */
    List<AmArchivesFailure> findByMotorId_MotorId(String motorId);
    
    /**
     * 根据故障类型查询故障记录
     */
    List<AmArchivesFailure> findByFailureTypeContaining(String failureType);
    
    /**
     * 根据故障发生时间范围查询故障记录
     */
    List<AmArchivesFailure> findByFailureTimeBetween(Date startTime, Date endTime);
    
    /**
     * 查询未修复的故障记录（repairTime为null）
     */
    List<AmArchivesFailure> findByRepairTimeIsNull();
    
    /**
     * 根据关键字在故障描述或故障原因中进行模糊搜索
     */
    @Query("SELECT f FROM AmArchivesFailure f WHERE f.failureDescription LIKE %:keyword% OR f.failureCause LIKE %:keyword%")
    List<AmArchivesFailure> searchByKeyword(@Param("keyword") String keyword);
    
    /**
     * 查询在指定时间范围内故障次数最多的电机
     */
    @Query(value = "SELECT motor_id, COUNT(*) as failure_count FROM am_archives_failure " +
            "WHERE failure_time BETWEEN :startTime AND :endTime " +
            "GROUP BY motor_id ORDER BY failure_count DESC LIMIT :limit", 
            nativeQuery = true)
    List<Object[]> findMostFrequentFailures(
            @Param("startTime") Date startTime, 
            @Param("endTime") Date endTime, 
            @Param("limit") int limit);
} 